package java6681.lesson11;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

/**
 * @author Junichiro
 * @description 执行SELECT语句
 * @date 2021-05-23 11:58
 **/

public class Select6681 {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    private static Properties prop;

    static {
        try {
            /*1.创建Properties集合类*/
            prop = new Properties();
            /*获取src路径下的文件的方式 ---> ClassLoader 类加载器*/
            ClassLoader classLoader = Select6681.class.getClassLoader();
            java.net.URL res = classLoader.getResource("java6681/config/jdbc.properties");
            String path = res.getPath();
            prop.load(new FileReader(path));
            /*3.获取数据，赋值*/
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            driver = prop.getProperty("driver");
            /*4.注册驱动*/
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要查找的名字中包含的任何信息：");
        String name = sc.nextLine();
        System.out.println("请输入id范围：");
        int idCount = sc.nextInt();
        find6681(name, idCount);
    }

    public static void find6681(String strFind, int maxId) {
        String nameResult = "%" + strFind + "%";
        String sql = "select id,sno,sname from xslist where sname like ? and id < ?";
        ResultSet rs = null;
        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql);
        ) {
            pstmt.setString(1, nameResult);
            pstmt.setInt(2, maxId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(1);
                String sno = rs.getString("sno");
                String sname = rs.getString("sname");
                System.out.println(id + ", " + sno + "," + sname);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }
    }
}
